﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for OtherDBConnection
/// </summary>
public class OtherDBConnection
{
    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataAdapter datatAdapter;
    OleDbDataReader dataReader;
    public String connString = "SELECT * FROM Other WHERE Other_Asset_ID = @Other_ID";



	public OtherDBConnection()
	{
        conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;
  	}


    public Other getOtherAssetByID(int otherAssetID)
    {
        connString = "SELECT * FROM Other WHERE Other_Asset_ID = @Other_ID";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Other_ID";
        computerIDParam.OleDbType = OleDbType.Numeric;
        computerIDParam.Size = 255;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = otherAssetID;

        cmd.Parameters.Add(computerIDParam);

        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Other other = new Other();
        while (dataReader.Read())
        {
            other.other_asset_ID = Int32.Parse(dataReader["Other_Asset_ID"].ToString());
            other.hardware_ID = Int32.Parse(dataReader["Hardware_ID"].ToString());
            other.description = dataReader["Description"].ToString();
            other.asset_Name = dataReader["Asset_Name"].ToString();
            other.brand_Name = dataReader["Brand_Name"].ToString();
            other.type = dataReader["Type"].ToString();
        }

        closeObjects();

        return other;
    }



    public DataSet getOtherDataSet()
    {
        DataSet ds = new DataSet();
        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Other;", conn);
        da.Fill(ds, "OtherTable");

        closeObjects();

        return ds;
    }

    public DataSet getOtherDataSet(int OtherID)
    {
        DataSet ds = new DataSet();
        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Other WHERE Other_Asset_ID = " + OtherID + ";", conn);
        da.Fill(ds, "OtherTable");

        closeObjects();

        return ds;
    }

    public DataSet getOtherDataSet_ByAdvStrSearch(String searchBy, String assetID)
    {
        DataSet ds = new DataSet();

        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Other WHERE " + searchBy + " = '" + assetID + "';", conn);
        da.Fill(ds, "OtherTable");

        closeObjects();

        return ds;
    }

    public void updateAll(Other other)
    {
        String connStringUpdate = "UPDATE Computers SET Description = '" + other.description
            + "', Brand_Name = '" + other.brand_Name
            + "', Asset_Name = '" + other.asset_Name 
            + "', Type = '" + other.type
            + "' WHERE Other_Asset_ID = " + other.other_asset_ID + ";";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }
    
    public void delete(Other otherID)
    {
        OleDbCommand myOleDbCommand;
        OleDbConnection myOleDbConnection = new OleDbConnection();
        myOleDbConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        String connString = "DELETE FROM Other WHERE Other_Asset_ID = " + otherID.other_asset_ID + ";";

        myOleDbCommand = new OleDbCommand(connString, myOleDbConnection);
        myOleDbConnection.Open();

        myOleDbConnection.Close();
    }

    public void closeObjects()
    {
        if (conn.State.ToString().Equals("Open"))
        {
            conn.Close();
        }
        if (cmd.Connection.State.ToString().Equals("Open"))
        {
            cmd.Connection.Close();
        }
    }



}